﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using DataAccess;
using System.Data.SqlClient;

/// <summary>
/// Summary description for QuestionDAL
/// </summary>
public class QuestionDAL
{
	public QuestionDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public bool Insert(Question question)
    {
        bool result = false;
        DbCommand cmd = GenericData.CreateCommand();
        cmd.CommandText = "INSERT INTO Question(Content, CategoryID, Type, Mark) VALUES('" + question.Content + "', '" + question.CategoryID + "', '" + question.Type + "', '" + question.Mark + "')";
        if (GenericData.ExecuteNoneQuery(cmd) > 0)
            result = true;
        return result;
    }

    public bool Delete(int QuestionID)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection scon = new SqlConnection("SERVER=.;DATABASE=AptitudeTest;UID=sa;PWD=1234$");
        scon.Open();
        transaction = scon.BeginTransaction();
         try
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM Answer WHERE QuestionID = " + QuestionID, scon, transaction);
            if (cmd.ExecuteNonQuery() > 0)
            {
                SqlCommand cmdDeleteQuestion = new SqlCommand("DELETE FROM Question WHERE ID = " + QuestionID, scon, transaction);
                if (cmdDeleteQuestion.ExecuteNonQuery() > 0)
                {
                    result = true;
                    transaction.Commit();
                }
            }
        }
         catch (SqlException sqlError)
         {
             transaction.Rollback();
         }
         scon.Close();
        return result;
    }


    public bool Update(Question question)
    {
        bool result = false;
        DbCommand cmd = GenericData.CreateCommand();
        cmd.CommandText = "UPDATE Question SET CategoryID = '" + question.CategoryID + "', Content = '" + question.Content + "', Type = " + question.Type + ", Mark = " + question.Mark + " WHERE ID = " + question.ID;
        if (GenericData.ExecuteNoneQuery(cmd) > 0)
            result = true;
        return result;
    }

    public static DataTable SearchQuestion(string QuestionContent)
    {
        DataTable tblData = new DataTable();

        string sql = String.Format("SELECT Question.ID, Question.Content, Question.Mark, Question.Type, Question.CategoryID, Category.CategoryName , case when Question.Type = 1 then 'Multiple' when Question.Type = 0 then 'Single' End 'TypeQuestion' FROM Question LEFT JOIN Category ON Question.CategoryID = Category.ID WHERE Content LIKE '%{0}%'", QuestionContent);
        
        ConnectDAL conDal = new ConnectDAL();
        tblData = conDal.getData(sql);

        return tblData;
    }

}